package server;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.HashMap;
import java.util.Map;

/**
 * The Class ServerProduceReport.
 * @author yudku
 */
public class ServerProduceReport {
	
	/** The scheme. */
	private String scheme;
	
	/** The password. */
	private String password;
	
	/** The username. */
	private String username;
	
	/** The con. */
	private Connection con;
	
	static int flag1 = 0;
	static int flag2 = 0;
	static int flag3 = 0;
	/**
	 * Instantiates a new server produce report.
	 *
	 * @param scheme the scheme
	 * @param password the password
	 * @param username the username
	 */
	public ServerProduceReport (String scheme, String password, String username)
	{
		this.scheme = scheme;
		this.password = password;
		this.username = username;
	}
	
	/**
	 * Daily statistic.
	 *
	 * @throws SQLException the SQL exception
	 */
	public void dailyStatistic () throws SQLException
	{
		if 	(ServerProduceReport.flag1 != 0)
		{
		int UserCount;
		int DownloadCount = 0;
		int Archivesize;
		int NewPhoto = 0;
		
		Calendar cal = Calendar.getInstance();	
		int currMonth = cal.get(Calendar.MONTH);
		currMonth++;
		int currYear = cal.get(Calendar.YEAR);
		int day = cal.get(Calendar.DAY_OF_MONTH);
		
		ServerComunicator SERVERhandler = new ServerComunicator ();	
		SERVERhandler.connectToDB (scheme, password, username);	
		con = SERVERhandler.getDBConnection();
		
		Statement stmt = con.createStatement();
				String Search = " SELECT *"
                				 +" FROM user ";
		
				ResultSet rs = stmt.executeQuery(Search);
				SerializedRS srs = new SerializedRS(rs);
				ArrayList<HashMap<String, String>> data = srs.getRowSet();
		
		UserCount = data.size();
		
			   Search = " SELECT *"
				 				 +" FROM customer_photos ";
		
			   rs = stmt.executeQuery(Search);
			   srs = new SerializedRS(rs);
			   data = srs.getRowSet();
			   int TotalDownToday = data.size();
			   
			   Search = " SELECT *"
					   			 +" FROM daily_statistc ";

			   rs = stmt.executeQuery(Search);
			   srs = new SerializedRS(rs);
			   data = srs.getRowSet();
			   Map<String, String> daily  = data.get(data.size()-1);
			   int TotalDownYesterday = Integer.parseInt(daily.get("DOWNLOADED"));
			   
			   DownloadCount = TotalDownToday - TotalDownYesterday;
			   
			   if (DownloadCount < 0)
				   DownloadCount = DownloadCount * (-1);
			   
			   Search = " SELECT *"
		 				 		 +" FROM photos ";
			   rs = stmt.executeQuery(Search);
			   srs = new SerializedRS(rs);
			   data = srs.getRowSet();
			   
		Archivesize = data.size();
		   				   		

		   	   for(int i = 0 ; i < data.size()  ; i++)
		   	   {
		   	       Map<String, String> Tupple  = data.get(i);	
		   			
		 		   int DBmonth = (Integer.parseInt((Tupple.get("DATE")).substring(5, 7)));
				   int DByear = (Integer.parseInt((Tupple.get("DATE")).substring(0, 4)));
				   int DBday = (Integer.parseInt((Tupple.get("DATE")).substring(8, 10)));
				   
		 		   if ((currMonth == DBmonth) && (currYear == DByear) && ( DBday == day))
		 			   NewPhoto++;
				}
		   		
		String insertQuery = "INSERT INTO daily_statistc "
									+"(memberNum ,downloaded, archiveSize, newPhoto) VALUES "
									+"("+ UserCount +","+ DownloadCount +","+ Archivesize +","+ NewPhoto +")";
		
		stmt.executeUpdate(insertQuery);
		con.close();
		}
		else
			ServerProduceReport.flag1++;
	}
	
	/**
	 * Weekly statistic.
	 *
	 * @throws SQLException the SQL exception
	 */
	public void weeklyStatistic () throws SQLException
	{
		if 	(ServerProduceReport.flag2 != 0)
		{
		int [] WeeklyArrayDown = new int [7];
		for(int i = 0 ; i < 7  ; i++)
			WeeklyArrayDown[i] = 0;
		
		int [] WeeklyArrayUp = new int [7];
		for(int i = 0 ; i < 7  ; i++)
			WeeklyArrayUp[i] = 0;
		
		double medianDown;
		double medianUp;
		
		double standDevDown;
		double standDevUp;
		
		Calendar cal = Calendar.getInstance();	
		int currMonth = cal.get(Calendar.MONTH);
		currMonth++;
		int currYear = cal.get(Calendar.YEAR);
		int day = cal.get(Calendar.DAY_OF_MONTH);
		
		ServerComunicator SERVERhandler = new ServerComunicator ();	
		SERVERhandler.connectToDB (scheme, password, username);	
		con = SERVERhandler.getDBConnection();
		
			Statement stmt = con.createStatement();
			String Search = " SELECT *"
									 +" FROM daily_statistc ";

			ResultSet rs = stmt.executeQuery(Search);
			SerializedRS srs = new SerializedRS(rs);
			ArrayList<HashMap<String, String>> data = srs.getRowSet();
			
		   	for(int i = 0 ; i < data.size()  ; i++)
		   	{
		   	      Map<String, String> Tupple  = data.get(i);	
		   			
		 		  int DBmonth = (Integer.parseInt((Tupple.get("DATE")).substring(5, 7)));
				  int DByear = (Integer.parseInt((Tupple.get("DATE")).substring(0, 4)));
				  int DBday = (Integer.parseInt((Tupple.get("DATE")).substring(8, 10)));
				   
		 		  if ((currMonth == DBmonth) && (currYear == DByear) && (DBday < day) && (DBday > (day - 7)))
		 		  {
		 			 WeeklyArrayDown [ day - DBday -1] = Integer.parseInt((Tupple.get("DOWNLOADED")));
		 		  	 WeeklyArrayUp [ day - DBday -1] = Integer.parseInt((Tupple.get("NEWPHOTO")));
		 		  }	  
			}
		   	standDevDown = getStdDev(WeeklyArrayDown, WeeklyArrayDown.length);
		   	standDevUp = getStdDev(WeeklyArrayUp, WeeklyArrayUp.length);
		   	
		   	Arrays.sort( WeeklyArrayDown );
		    Arrays.sort( WeeklyArrayUp );
		    medianDown = (double) WeeklyArrayDown[(WeeklyArrayDown.length/2) + 1];
		    medianUp = (double) WeeklyArrayUp[(WeeklyArrayUp.length/2) + 1];
		    
		    standDevDown = Math.floor(standDevDown * 100) / 100;
		    standDevUp = Math.floor(standDevUp * 100) / 100;
		    medianDown = Math.floor(medianDown * 100) / 100;
		    medianUp = Math.floor(medianUp * 100) / 100;
			String insertQuery = "INSERT INTO weekly "
					+"(medianDown ,standardDown, medianADD, standardADD) VALUES "
					+"("+ medianDown +","+ standDevDown +","+ medianUp +" ,"+ standDevUp +")";

			stmt.executeUpdate(insertQuery);
			con.close();
		}
		else
			 ServerProduceReport.flag2++;
	}

	
	
	/**
	 * Yearly statistic.
	 *
	 * @throws SQLException the SQL exception
	 */
	public void yearlyStatistic () throws SQLException
	{
		if 	(ServerProduceReport.flag3 != 0)
		{
		int [] yearlyArrayMem = new int [365];
		for(int i = 0 ; i < 365  ; i++)
			yearlyArrayMem[i] = 0;
		
		int [] yearlyArrayPack = new int [12];
		for(int i = 0 ; i < 12  ; i++)
			yearlyArrayPack[i] = 0;
		
		int [] yearlyArrayUsage = new int [12];
		for(int i = 0 ; i < 12  ; i++)
			yearlyArrayUsage[i] = 0;
		
		double medianMember;
		double medianPack;
		double medianUsage;
		
		double standDevMember;
		double standDevPack;
		double standDevUsage;
		
		
		
		Calendar cal = Calendar.getInstance();	
		int currMonth = cal.get(Calendar.MONTH);
		currMonth++;
		int currYear = cal.get(Calendar.YEAR);
		int day = cal.get(Calendar.DAY_OF_MONTH);
		
		ServerComunicator SERVERhandler = new ServerComunicator ();	
		SERVERhandler.connectToDB (scheme, password, username);	
		con = SERVERhandler.getDBConnection();

		
			Statement stmt = con.createStatement();
			String Search = " SELECT *"
									 +" FROM daily_statistc ";

			ResultSet rs1 = stmt.executeQuery(Search);
			SerializedRS srs1 = new SerializedRS(rs1);
			ArrayList<HashMap<String, String>> data = srs1.getRowSet();
			
		   	for(int i = 0 ; i < data.size()  ; i++)
		   	{
		   	      Map<String, String> Tupple  = data.get(i);	
		   			
		 		  int DBmonth = (Integer.parseInt((Tupple.get("DATE")).substring(5, 7)));
				  int DByear = (Integer.parseInt((Tupple.get("DATE")).substring(0, 4)));
				  int DBday = (Integer.parseInt((Tupple.get("DATE")).substring(8, 10)));
				   
		 		  if (currYear == DByear)
		 		  {
		 			 yearlyArrayMem [ DBday + (DBmonth * 30)-1 ] = Integer.parseInt((Tupple.get("MEMBERNUM")));
		 		  }		 		  
		 		  
			}
		   	standDevMember = getStdDev(yearlyArrayMem, yearlyArrayMem.length);	   	
		   	Arrays.sort( yearlyArrayMem );
		    medianMember = (double) yearlyArrayMem[(yearlyArrayMem.length/2) + 1];

			
		    // ******************************************************** new calculation ******************************************
		    
		    
				stmt = con.createStatement();
					   Search = " SELECT *"
										 +" FROM package_report";

				ResultSet rs2 = stmt.executeQuery(Search);
				SerializedRS srs2 = new SerializedRS(rs2);
				ArrayList<HashMap<String, String>> data2 = srs2.getRowSet();
				
			   	for(int i = 0 ; i < data2.size()  ; i++)
			   	{
			   	      Map<String, String> Tupple  = data2.get(i);	
			   			
			 		  int DBmonth = (Integer.parseInt((Tupple.get("DATE")).substring(5, 7)));
					  int DByear = (Integer.parseInt((Tupple.get("DATE")).substring(0, 4)));
					  int DBday = (Integer.parseInt((Tupple.get("DATE")).substring(8, 10)));
					   
			 		  if (currYear == DByear)
			 		  {
			 			 yearlyArrayPack [ DBmonth-1 ] = Integer.parseInt((Tupple.get("NEWPACK")));
			 			 yearlyArrayUsage [ DBmonth-1 ] = Integer.parseInt((Tupple.get("PACKUSAGE")));
			 		  }		 		  
			 		  
				}
			   	
			   	standDevPack = getStdDev(yearlyArrayPack, yearlyArrayPack.length);	
			   	Arrays.sort( yearlyArrayPack );
			    medianPack = (double) yearlyArrayPack[(yearlyArrayPack.length/2) + 1];
			    
			   	standDevUsage = getStdDev(yearlyArrayUsage, yearlyArrayUsage.length);	
			   	Arrays.sort( yearlyArrayPack );
			    medianUsage = (double) yearlyArrayUsage[(yearlyArrayUsage.length/2) + 1];
	    
			    
			standDevMember = Math.floor(standDevMember * 100) / 100;
			medianMember = Math.floor(medianMember * 100) / 100;
			standDevPack = Math.floor(standDevPack * 100) / 100;
			medianPack = Math.floor(medianPack * 100) / 100;
			standDevUsage = Math.floor(standDevPack * 100) / 100;
			medianUsage = Math.floor(medianPack * 100) / 100;
		    
		    
			String insertQuery ="INSERT INTO yearly "
					+"(medianReg ,standardReg, medianSub, standardSub, medianUsage, standardUsage) VALUES "
					+"("+ medianMember +","+ standDevMember +","+ medianPack +","+ standDevPack +" ,"+ medianUsage +" ,"+ standDevUsage +")";

			stmt.executeUpdate(insertQuery);
			con.close();
		}
		else
			ServerProduceReport.flag3++;
	}
	
    /**
     * Gets the mean.
     *
     * @param data the data
     * @param size the size
     * @return the mean
     */
    private double getMean(int [] data, int size)
    {
        double sum = 0.0;
        for(double a : data)
            sum += a;
        return sum/size;
    }

    /**
     * Gets the variance.
     *
     * @param data the data
     * @param size the size
     * @return the variance
     */
    private double getVariance(int [] data, int size)
    {
        double mean = getMean(data, size);
        double temp = 0;
        for(double a :data)
            temp += (mean-a)*(mean-a);
        return temp/size;
    }

    /**
     * Gets the std dev.
     *
     * @param data the data
     * @param size the size
     * @return the std dev
     */
    private double getStdDev(int [] data, int size)
    {
        return Math.sqrt(getVariance(data, size));
    }
}
